# -*- coding: utf-8 -*-

from utils.DBService import sqlquery
import os

DIBANG_SQL = """select a.ID,a.departname,a.parentdepartid,b.gprsbianhao,b.banhezhanminchen 
from t_s_depart as a, WZDibangxinxi as b
where a.ID=b.departid and a.ID in ('{}')"""

CAILIAO_SQL = """select c.departid,d.departname, a.cailiaoNo,b.cailiaoName, SUM(cast(a.jingzhongT as FLOAT))
from WZYCLJinChangGB as a, WZCaiLiaoNameDict as b, WZDibangxinxi as c, t_s_depart as d
where a.cailiaoNo=b.cailiaoNo and a.shebeibianhao = c.gprsbianhao and c.departid=d.ID 
{} 
and a.shebeibianhao in ('{}')
{}
group by c.departid,d.departname, a.cailiaoNo,b.cailiaoName
"""

CAILIAONO_SQL = """select cailiaoNo from WZCaiLiaoNameDict where parentNode in (select cailiaoNo from WZCaiLiaoNameDict where cailiaoName in (\'{}\'))"""

DEP_SQL = """WITH  dept
    AS (SELECT  *
        FROM   t_s_depart
        WHERE  parentdepartid = '{}'
        UNION ALL
        SELECT  d.*
        FROM   t_s_depart d
            INNER JOIN dept ON d.parentdepartid = dept.id
						 )
  SELECT id
  FROM  dept"""

def inputStatic(Args,user):
    print('Input参数:\n\t', Args)
    depId = Args['depId']
    dates = Args['dates']
    materials = Args['materials']
    # query dibang
    depId = '\',\''.join(depId.split(','))
    if depId == 'All':
        deps = sqlquery(DEP_SQL.format(user))
        print('SQL:\n\t', DEP_SQL.format(user))
        print(deps)
        depsid = []
        for dep in deps:
            depsid.append(dep[0])
        depId = '\',\''.join(depsid)
    dibangSql = DIBANG_SQL.format(depId)
    print('SQL:\n\t', dibangSql)
    dibangData = sqlquery(DIBANG_SQL.format(depId))
    dibangNo = []
    for dibang in dibangData:
        dibangNo.append(dibang[3])
    print('Data:\n\t', dibangNo)
    # query jinchang
    materials = '\',\''.join(materials.split(','))
    print(materials)
    print('SQL:\n\t', CAILIAONO_SQL.format(materials))
    cl = sqlquery(CAILIAONO_SQL.format(materials))
    print(cl)
    if cl.__len__() > 0:
        cls = []
        for c in cl:
            cls.append(c[0])
        materials = '\',\''.join(cls)
        clWhere = 'and a.cailiaoNo in (\'{}\') '.format(materials)
    else:
        clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials!='All' else ''
    # clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials!='All' else ''
    dateWhere = 'and CONVERT(nvarchar(10), jinchangshijian, 120)in (\'{}\')'
    datearray = dates.split(',')
    if len(datearray) == 2:
        dateWhere = 'and CONVERT(nvarchar(10), jinchangshijian, 120)>=\'{}\' and CONVERT(nvarchar(10), jinchangshijian, 120)<=\'{}\''.format(datearray[0],datearray[1])
    else:
        dateWhere = dateWhere.format('\',\''.join(dates.split(',')))
    # dates = '\',\''.join(dates.split(','))
    dibangNo = '\',\''.join(dibangNo)
    cailiaoSql = CAILIAO_SQL.format(clWhere, dibangNo, dateWhere)
    print('CL_SQL:\n\t', cailiaoSql)
    cailiaoData = sqlquery(cailiaoSql)
    clData = {}
    for cailiao in cailiaoData:
        depName, clName, weight = '', '', 0
        if os.environ.get("ENV_NAME")=='1':
            depName, clName, weight = cailiao[1], cailiao[3], round(cailiao[4], 2)
        else:
            depName, clName, weight = cailiao[1], cailiao[3].encode('latin1').decode('gbk'), round(cailiao[4], 2)
        if depName not in clData:
            clData[depName] = {"materials":[], 'weights':[]}
        if weight > 0:
            clData[depName]['materials'].append(clName)
            clData[depName]['weights'].append(weight)
    return clData


functionConf = {
    "name": "inputStatic",
    "description": "Query material entry weighing data based on depart ID, time, and material name",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs, multiple departments separated by commas in English, default is All",
            },
            "dates": {
                "type": "string",
                "description": "The time of the material, using yyyy-MM-dd format, with multiple dates separated by commas, for example:2023-07-14,2023-07-15"
            },
            "materials": {
                "type": "string",
                "description": "Item names, multiple items separated by commas, all items using 'All', such as fly ash, cement"
            }
        },
        "required": [
            "depId",
            "dates",
            "materials"
        ]
    }
}
